package com.scse.bms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.scse.bms.domain.CommentVO;
import com.scse.bms.entity.CommentDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Title: CommentMapper
 * @Author: 吴赠禹
 * @Date: 2021/2/25 13:05
 * @Version: 1.0
 * @Description: TODO
 */


@Repository
@Mapper
public interface CommentMapper extends BaseMapper<CommentDO> {


    /**
     * 通过审核
     * @param commentDO
     * @return
     */
    @Update("update t_comment set status = 1 where id = #{id}")
    int approved(CommentDO commentDO);


    /**
     * 后台获取所有评论（即book_id, user_id, content 不为空，正常情况下都不为空，除非是测试数据）
     * @Select("select id, book_id, user_id, content, created, status" +
     *             " from t_comment where book_id is not null and user_id is not null")
     *
     * 改为联合查询（显示评论者用户名，被评论的书名，涉及三个表）
     */
    @Select("select a.id,a.content,a.created,a.status,b.username,c.title FROM " +
            "(select id,user_id,book_id,content,created,status from t_comment " +
            "where book_id is not null and user_id is not null) a " +
            "left join t_user b on a.user_id = b.id " +
            "left join t_book c on a.book_id = c.id")
    IPage<CommentDO> selectAll(Page<?> page);


    /**
     * 改变审核状态
     * @param commentDO
     * @return
     */
    @Update("update t_comment set status = #{status} where id = #{id}")
    int changeStatus(CommentDO commentDO);


    /**
     * 前台展示评论列表，使用CommentVO
     * 根据书ID和审核状态（通过）获取评论列表，只需要bookId
     */
    @Select("SELECT a.id,content,created,src,username FROM " +
            "(SELECT id,book_id,user_id,content,created,STATUS FROM " +
            "t_comment WHERE book_id = #{bookId} AND STATUS = 1) a " +
            "LEFT JOIN t_user b ON a.user_id = b.id " +
            "LEFT JOIN  " +
            "(select u.id as user_id,h.src as src FROM " +
            "t_user u LEFT JOIN t_user_head_img uh on u.id = uh.user_id " +
            "LEFT JOIN t_head_img h on uh.head_img_id = h.id) c ON b.id = c.user_id " +
            "ORDER BY created DESC")
    List<CommentVO> selectCommentsForFrontShow(Integer bookId);


}
